/*
 *中北大学软件学院 版权所有2021 
 */
package com.ychs.uolab.user.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ychs.uolab.user.vo.User;
import com.ychs.uolab.util.DbUtil;
import com.ychs.uolab.util.DbUtilJdbc;

/**
 *
 * @author cyq
 * @version 1.0
 * @date 2021年1月26日
 */
public class UserDaoImpl implements UserDao {

	@Override
	public boolean login(String userName, String userPsw) {
		// TODO Auto-generated method stub
		//数据库连接部分
		Connection conn = null;
	    //结果集，查询得到的结果
		ResultSet rs = null;
	    	
		PreparedStatement pstmt = null;
		boolean loginflag = true;
	
		//打开数据库连接
		try {
			conn = DbUtil.getConnection();
			//准备sql语句,其中的问号叫占位符
			String sql = "select * from t_user where username = ? and password = ?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, userName);
			pstmt.setString(2, userPsw);
			//执行sql语句，下达执行命令
			rs = pstmt.executeQuery();
			
			//rs里有数据的话，返回true，否则返回false
			if (rs.next()) {
				loginflag = true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(rs, pstmt, conn);
		}
		
		
		return loginflag;
	}

	/**
	 * 
	 */
	
	@Override
	public boolean insertUser(User user) {
		// TODO Auto-generated method stub
		boolean flag = false;
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = DbUtil.getConnection();
			String sql = "insert into t_user values(?,?,?,?,?,?,?,?,?,?,?,?,?,?);";
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, user.getUid());
			pstmt.setString(2, user.getUsername());
			pstmt.setString(3, user.getPasword());
			pstmt.setString(4, user.getRealname());
			pstmt.setString(5, user.getSex());
			pstmt.setString(6, user.getTelephone());
			pstmt.setString(7, user.getDepartmentName());
			pstmt.setString(8, user.getMajorName());
			pstmt.setString(9, user.getCname());
			pstmt.setString(10, user.getGrade());
			pstmt.setString(11, user.getDuty());
			pstmt.setString(12, user.getQq());
			pstmt.setString(13, user.getStatus());
			pstmt.setString(14, user.getRemark());
			
		    int count = pstmt.executeUpdate();
			
			if (count!=0) {
				flag = true;
			}
		    
		    
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(null, pstmt, conn);
		}
		
		return flag;
	}

	@Override
	public int updateUser(User user) {
		// TODO Auto-generated method stub
		Connection conn = null;
		PreparedStatement pstmt = null;
		int count = 0;
		
		try {
			String sql = "UPDATE t_user SET telephone=?,qq=?,duty=?,remark=? WHERE uid=?;";
			
			conn = DbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, user.getTelephone());
			pstmt.setString(2, user.getQq());
			pstmt.setString(3, user.getDuty());
			pstmt.setString(4, user.getRemark());
			pstmt.setInt(5, user.getUid());
		    
			//count表示执行的sql语句影响的记录行数
			count = pstmt.executeUpdate();  
		}catch(Exception e) {
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(null, pstmt, conn);
		}
		
		return count;
	}

	@Override
	public int deleteuser(int userid) {
		// TODO Auto-generated method stub
	    Connection conn = null;
	    PreparedStatement pstmt = null;
	    int count = 0;
	    
	    String sql = "DELETE FROM t_user WHERE uid=?;";
        
	    try {
	    	conn = DbUtil.getConnection();
	    	pstmt = conn.prepareStatement(sql);
	    	pstmt.setInt(1, userid);
	    	
	    	count = pstmt.executeUpdate();
	    	
	    }catch(Exception e) {
	    	e.printStackTrace();
	    }finally {
	        DbUtil.closeResource(null, pstmt, conn);	
	    }
	    	
		return count;
	}

	@Override
	public List<User> selectUser(String username, String dname, String major, String classes, String grade) {
		// TODO Auto-generated method stub
		Connection conn = null;
		//非预编译
		Statement stmt = null;
		ResultSet rs = null;
		List<User> userList = new ArrayList<User>();
		

		
		try {
			conn = DbUtil.getConnection();
			
			//我们目前不知道用户具体填了几个，座椅sql语句是动态变化的
			String sql = "SELECT * FROM t_user WHERE 1=1 ";
			//这个是可变字符串类
			StringBuilder strBuilder = new StringBuilder(sql);
	 		
			if (username != null){	
	 		   strBuilder.append(" AND username='"+username+"'");
	 		}
			
			if (dname != null) {
				strBuilder.append(" AND dname='"+dname+"'");
			}
			
			if (major != null) {
				strBuilder.append(" AND major_name='"+major+"'");
			}
			
			if (classes != null) {
				strBuilder.append(" AND cname='"+classes+"'");
			}
			if (grade != null) {
				strBuilder.append(" AND grade='"+grade+"'");
			}
			
			sql = strBuilder.toString();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(sql);
			 
			//把rs结果集里面的数据转移到集合userList中
			while (rs.next()) {
				User user = new User();
				//rs.get里面是数据库里的字段
				user.setUsername(rs.getString("username"));
			    user.setDepartmentName(rs.getString("dname"));
			    user.setMajorName(rs.getString("major_name"));
			    user.setCname(rs.getString("cname"));
			    user.setGrade(rs.getString("grade"));
			    user.setTelephone(rs.getString("telephone"));
			    user.setQq(rs.getString("qq"));
			    user.setDuty(rs.getString("duty"));
			    user.setStatus(rs.getString("status"));
			    userList.add(user);
				
				
			}
				
		}catch (Exception e) {
			e.printStackTrace();
		}finally {
			DbUtil.closeResource(rs, null, conn);
		}
		
		return userList;
	}
	
}
